﻿/*******************************************************************************
* Copyright (C) sykjwh.cn
* 
* Author: liuxiang
* Create Date: 2019/03/27
* Description: Automated building by liuxiang20041986@qq.com 
* http://www.sykjwh.cn/
*********************************************************************************/

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace Sykj.Repository
{
    /// <summary>
    /// DatabaseFacade扩展类
    /// </summary>
    public static class DatabaseFacadeExtension
    {
        #region 执行一条计算查询结果语句，返回查询结果
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果
        /// </summary>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            var conn = facade.GetDbConnection();
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                var command = conn.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddRange(parameters);
                object obj = command.ExecuteScalar();
                command.Parameters.Clear();
                if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value)))
                {
                    conn.Close();
                    return null;
                }
                else
                {
                    conn.Close();
                    return obj;
                }
            }
            catch (DbException exc)
            {
                conn.Close();
                throw exc;
            }
        }
        #endregion

        #region  执行一条计算查询结果语句，查询返回对象列表
        /// <summary>
        /// 执行一条计算查询结果语句，查询返回对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
        {
            var conn = facade.GetDbConnection();
            try
            {
                if (conn.State!= ConnectionState.Open) conn.Open();
                var command = conn.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddRange(parameters);
                var propts = typeof(T).GetProperties();
                var modelList = new List<T>();
                T model;
                object val;
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        model = new T();
                        foreach (var l in propts)
                        {
                            if (ReaderExists(reader,l.Name))
                            {
                                val = reader[l.Name];
                                if (val == DBNull.Value)
                                {
                                    l.SetValue(model, null);
                                }
                                else
                                {
                                    if (l.PropertyType.ToString() == "System.Int32")
                                    {
                                        l.SetValue(model, int.Parse(val.ToString()));
                                    }
                                    else if (l.PropertyType.ToString() == "System.Nullable`1[System.Int32]")
                                    {
                                        l.SetValue(model, int.Parse(val.ToString()));
                                    }
                                    else if (l.PropertyType.ToString() == "System.Int64")
                                    {
                                        l.SetValue(model, long.Parse(val.ToString()));
                                    }
                                    else if (l.PropertyType.ToString() == "System.Nullable`1[System.Int64]")
                                    {
                                        l.SetValue(model, long.Parse(val.ToString()));
                                    }
                                    else if (l.PropertyType.ToString() == "System.DateTime")
                                    {
                                        l.SetValue(model, Convert.ToDateTime(val));
                                    }
                                    else if (l.PropertyType.ToString() == "System.Boolean")
                                    {
                                        l.SetValue(model, Convert.ToBoolean(val));
                                    }
                                    else if (l.PropertyType.ToString() == "System.Single")
                                    {
                                        l.SetValue(model, Convert.ToSingle(val));
                                    }
                                    else if (l.PropertyType.ToString() == "System.Double")
                                    {
                                        l.SetValue(model, Convert.ToDouble(val));
                                    }
                                    else
                                    {
                                        l.SetValue(model, val);
                                    }
                                }
                            }
                        }
                        modelList.Add(model);
                    }
                }
                conn.Close();
                return modelList;
            }
            catch (DbException exc)
            {
                conn.Close();
                throw exc;
            }
        }
        #endregion

        #region 判断查询的数据是否包含实体列 ydp
        /// <summary>
        /// 判断查询的数据是否包含实体列
        /// </summary>
        /// <param name="dr">DbDataReader</param>
        /// <param name="columnName">字段名</param>
        /// <returns></returns>
        private static bool ReaderExists(DbDataReader dr, string columnName)
        {
            dr.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
            return dr.GetSchemaTable().DefaultView.Count > 0;
        }
        #endregion

        #region  执行一条计算查询结果语句，查询返回DataTable
        /// <summary>
        /// 执行一条计算查询结果语句，查询返回对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable DataTable(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            var conn = facade.GetDbConnection();
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                var command = conn.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddRange(parameters);
                var reader = command.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(reader);
                reader.Close();
                conn.Close();
                return dt;
            }
            catch (DbException exc)
            {
                conn.Close();
                throw exc;
            }
        }
        #endregion
    }
}
